﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using MySql.Data.MySqlClient;
using Model;

namespace DAL
{
    public class BlogDAL
    {


        /// <summary>
        /// 分页
        /// </summary>
        /// <param name="extsql">sql条件</param>
        /// <param name="order">排序</param>
        /// <param name="pageIndex">页数</param>
        /// <param name="pageCount">条数</param>
        /// <param name="totalPages">总页数</param>
        /// <param name="totalItems">总条数</param>
        /// <returns></returns>
        public List<Blog> GetPage(string extsql, string order, int pageIndex, int pageCount, ref int totalPages, ref int totalItems)
        {
            StringBuilder sql = new StringBuilder();
            sql.Append(" select blog_id,blog_title,blog_subtitle,blog_adminid,mem_name,blog_addtime,blog_delete,blog_tags,blog_cate,blog_type from blog  ");
            sql.Append(" left join member on mem_id=blog_adminid ");
            sql.Append("where blog_delete=0 ");
            sql.Append(extsql);
            if (!string.IsNullOrEmpty(order))
            {
                sql.Append(" order by " + order);
            }
            sql.Append(" limit " + ((pageIndex - 1) * pageCount) + "," + pageCount + ";");
            sql.Append("SELECT COUNT(*) FROM  blog left join member on mem_id=blog_adminid where blog_delete=0 " + extsql + ";");

            return DBUtility.MySqlHelper.ExecuteDataReaderPage<Blog>(sql.ToString(), pageCount, ref totalPages, ref totalItems);
        }

        /// <summary>
        /// 获取一条数据
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public Blog GetModel(string id)
        {
            StringBuilder sql = new StringBuilder();
            sql.Append("select blog.*,mem_name from blog ");
            sql.Append(" left join member on mem_id=blog_adminid ");
            sql.Append("where blog_delete=0 and blog_id=@id");
            return DBUtility.MySqlHelper.ExecuteDataReaderModel<Blog>(sql.ToString(), new MySqlParameter("@id", id));
        }

        /// <summary>
        /// 获取自定义数据
        /// </summary>
        /// <param name="columns">列名</param>
        /// <param name="where">条件 </param>
        /// <returns></returns>
        public List<dynamic> GetList(string column,string where)
        {
            string sql = string.Format("select {0} from blog where 1=1 {1}", column, where);
            return DBUtility.MySqlHelper.ExecuteDynamic(sql);
        }

        /// <summary>
        /// 添加
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public int Add(Blog model)
        {
            MySqlParameter[] para = {
                new MySqlParameter("@blog_title",model.blog_title),
                new MySqlParameter("@blog_subtitle",model.blog_subtitle),
                new MySqlParameter("@blog_content",model.blog_content),
                new MySqlParameter("@blog_adminid",model.blog_adminid),
                new MySqlParameter("@blog_addtime",model.blog_addtime),
                new MySqlParameter("@blog_delete",model.blog_delete),
                new MySqlParameter("@blog_tags",model.blog_tags),
                new MySqlParameter("@blog_cate",model.blog_cate),
                new MySqlParameter("@blog_type",model.blog_type)
            };
            return DBUtility.MySqlHelper.ExecuteNonQuery(" insert into blog (blog_title,blog_subtitle,blog_content,blog_adminid,blog_addtime,blog_delete,blog_tags,blog_cate,blog_type) Values(@blog_title,@blog_subtitle,@blog_content,@blog_adminid,@blog_addtime,@blog_delete,@blog_tags,@blog_cate,@blog_type)", para);
            
        }
        /// <summary>
        /// 更新
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public int Update(Blog model)
        {
            MySqlParameter[] para = {
                new MySqlParameter("@blog_title",model.blog_title),
                new MySqlParameter("@blog_subtitle",model.blog_subtitle),
                new MySqlParameter("@blog_content",model.blog_content),
                new MySqlParameter("@blog_tags",model.blog_tags),
                new MySqlParameter("@blog_cate",model.blog_cate),
                new MySqlParameter("@blog_type",model.blog_type),
                new MySqlParameter("@blog_id",model.blog_id)
            };
            return DBUtility.MySqlHelper.ExecuteNonQuery("update blog set blog_title=@blog_title,blog_subtitle=@blog_subtitle,blog_content=@blog_content,blog_tags=@blog_tags,blog_cate=@blog_cate,blog_type=@blog_type where blog_id=@blog_id", para);
        }

        /// <summary>
        /// 删除
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public int delete(int id)
        {
            return DBUtility.MySqlHelper.ExecuteNonQuery("update blog set blog_delete=1 where blog_id=@id", new MySqlParameter("@id", id));
        }

        /// <summary>
        /// 最近文章(5个)
        /// </summary>
        /// <returns></returns>
        public List<dynamic> RecentBlog()
        {
            return DBUtility.MySqlHelper.ExecuteDynamic("select blog_id,blog_title from blog where blog_delete=0 and blog_type=1 order by blog_addtime desc limit 5");
        }



    }
}
